package db

import (
	"database/sql"
	"embed"
	"fmt"
	"time"

	monproto "gollect/proto"

	_ "github.com/mattn/go-sqlite3"
)

//go:embed schema.sql
var schemaFS embed.FS

// DB represents the database connection
type DB struct {
	*sql.DB
}

// New creates a new database connection and initializes the schema
func New(dbPath string) (*DB, error) {
	sqlDB, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// Initialize schema
	schema, err := schemaFS.ReadFile("schema.sql")
	if err != nil {
		return nil, fmt.Errorf("failed to read schema: %w", err)
	}

	if _, err := sqlDB.Exec(string(schema)); err != nil {
		return nil, fmt.Errorf("failed to initialize schema: %w", err)
	}

	return &DB{sqlDB}, nil
}

// UpsertTrode inserts or updates a trode in the database
func (db *DB) UpsertTrode(id, address string, port int) error {
	_, err := db.Exec(`
        INSERT INTO trodes (id, address, port)
        VALUES (?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            address = excluded.address,
            port = excluded.port
    `, id, address, port)
	return err
}

// InsertMetric inserts a new metric into the database
func (db *DB) InsertMetric(trodeID, metricType string, value float64, timestamp time.Time) error {
	_, err := db.Exec(`
        INSERT INTO metrics (trode_id, metric_type, value, timestamp)
        VALUES (?, ?, ?, ?)
    `, trodeID, metricType, value, timestamp)
	return err
}

// InsertProcess inserts a new process snapshot into the database
func (db *DB) InsertProcess(trodeID string, process *monproto.JobInfo, timestamp time.Time) error {
	_, err := db.Exec(`
        INSERT INTO processes (trode_id, root_pid, name, cmd, cpu, memory, uptime_seconds, timestamp)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `, trodeID, process.RootPid, process.Name, process.Cmd, process.Cpu, process.Mem, process.UptimeSeconds, timestamp)
	return err
}

// InsertUser inserts a new user snapshot into the database
func (db *DB) InsertUser(trodeID string, user *monproto.UserInfo, timestamp time.Time) error {
	_, err := db.Exec(`
        INSERT INTO users (trode_id, username, cpu, memory, timestamp)
        VALUES (?, ?, ?, ?, ?)
    `, trodeID, user.User, user.Cpu, user.Mem, timestamp)
	return err
}

// GetMetrics retrieves metrics for a specific trode and time range
func (db *DB) GetMetrics(trodeID, metricType string, start, end time.Time) ([]time.Time, []float64, error) {
	rows, err := db.Query(`
        SELECT timestamp, value
        FROM metrics
        WHERE trode_id = ? AND metric_type = ? AND timestamp BETWEEN ? AND ?
        ORDER BY timestamp ASC
    `, trodeID, metricType, start, end)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	var timestamps []time.Time
	var values []float64

	for rows.Next() {
		var ts time.Time
		var val float64
		if err := rows.Scan(&ts, &val); err != nil {
			return nil, nil, err
		}
		timestamps = append(timestamps, ts)
		values = append(values, val)
	}

	return timestamps, values, rows.Err()
}

// GetLatestMetrics retrieves the latest metrics for all trodes
func (db *DB) GetLatestMetrics() (map[string]map[string]float64, error) {
	rows, err := db.Query(`
        SELECT trode_id, metric_type, value
        FROM latest_metrics
    `)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := make(map[string]map[string]float64)

	for rows.Next() {
		var trodeID, metricType string
		var value float64
		if err := rows.Scan(&trodeID, &metricType, &value); err != nil {
			return nil, err
		}
		if result[trodeID] == nil {
			result[trodeID] = make(map[string]float64)
		}
		result[trodeID][metricType] = value
	}

	return result, rows.Err()
}

// GetProcesses retrieves processes for a specific trode and time range
func (db *DB) GetProcesses(trodeID string, start, end time.Time) ([]*monproto.JobInfo, error) {
	rows, err := db.Query(`
        SELECT root_pid, name, cmd, cpu, memory, uptime_seconds
        FROM processes
        WHERE trode_id = ? AND timestamp BETWEEN ? AND ?
        ORDER BY timestamp DESC
        LIMIT 10
    `, trodeID, start, end)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var processes []*monproto.JobInfo
	for rows.Next() {
		proc := &monproto.JobInfo{}
		if err := rows.Scan(&proc.RootPid, &proc.Name, &proc.Cmd, &proc.Cpu, &proc.Mem, &proc.UptimeSeconds); err != nil {
			return nil, err
		}
		processes = append(processes, proc)
	}

	return processes, rows.Err()
}

// GetUsers retrieves users for a specific trode and time range
func (db *DB) GetUsers(trodeID string, start, end time.Time) ([]*monproto.UserInfo, error) {
	rows, err := db.Query(`
        SELECT username, cpu, memory
        FROM users
        WHERE trode_id = ? AND timestamp BETWEEN ? AND ?
        ORDER BY timestamp DESC
        LIMIT 10
    `, trodeID, start, end)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []*monproto.UserInfo
	for rows.Next() {
		user := &monproto.UserInfo{}
		if err := rows.Scan(&user.User, &user.Cpu, &user.Mem); err != nil {
			return nil, err
		}
		users = append(users, user)
	}

	return users, rows.Err()
}

// CleanupOldData removes data older than the specified duration
func (db *DB) CleanupOldData(age time.Duration) error {
	cutoff := time.Now().Add(-age)

	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Delete old metrics
	if _, err := tx.Exec("DELETE FROM metrics WHERE timestamp < ?", cutoff); err != nil {
		return err
	}

	// Delete old processes
	if _, err := tx.Exec("DELETE FROM processes WHERE timestamp < ?", cutoff); err != nil {
		return err
	}

	// Delete old users
	if _, err := tx.Exec("DELETE FROM users WHERE timestamp < ?", cutoff); err != nil {
		return err
	}

	return tx.Commit()
}
